package de.velvettear.squirrel.database;

import java.io.File;
import java.util.HashMap;
import java.util.Vector;

import net.sqlcipher.database.SQLiteDatabase;
import net.sqlcipher.database.SQLiteOpenHelper;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.util.Log;
import de.velvettear.squirrel.database.Column.ColumnDataType;
import de.velvettear.squirrel.kernel.NFCTag;
import de.velvettear.squirrel.kernel.Password;
import de.velvettear.squirrel.kernel.Settings;
import de.velvettear.squirrel.kernel.Statics;

public class DatabaseHelper extends SQLiteOpenHelper {

	protected static String ENCRYPTION_KEY;
	
	public DatabaseHelper(Context context, String databaseName, int version) {
		super(context, databaseName, null, version);
		ENCRYPTION_KEY = "";
//		EncryptionHelper.getStaticEncryptionKey();
	}

	public void onCreate(SQLiteDatabase database) {
		HashMap<String, Vector<Column>> neededTables = getAllTables();
		for (String m_sTableName : neededTables.keySet()) {
			try {
				createTable(m_sTableName, neededTables.get(m_sTableName));
			} catch (Exception e) {
				if (e.getMessage() != null) {
					Log.e("onCreate()", e.getMessage());
				}
			}
		}
	}

	public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
		
	}
	
	public SQLiteDatabase getWritableDatabase() {
		return super.getWritableDatabase(ENCRYPTION_KEY);
	}
	
	public SQLiteDatabase getReadableDatabase() {
		return super.getReadableDatabase(ENCRYPTION_KEY);
	}
	
	private static void createTable(String TableName, Vector<Column> columns) {
		Log.i("createTable()", "BEGIN");
		
		String sqlStatement = "CREATE TABLE IF NOT EXISTS " + TableName + " (";
		for (int i = 0; i < columns.size(); i++) {
			sqlStatement += columns.elementAt(i).getSqlDefinitionString();
			if (i < columns.size() - 1) {
				sqlStatement += ", ";
			}
		}
		sqlStatement += ");";

		try {
			Statics.DATABASEHELPER.getWritableDatabase().execSQL(sqlStatement);
			Log.i("createTable()", "TABLE " + TableName + " CREATED");
		} catch (Exception e) {
			if (e.getMessage() != null) {
				Log.e("createTable()", e.getMessage());
			}
		}
		
		Log.i("createTable()", "END");
	}

	public static void checkTables() {
		Log.i("checkTables()", "BEGIN");
		
		HashMap<String, String> existingTables = new HashMap<String, String>();
		HashMap<String, Vector<Column>> neededTables = getAllTables();

		try {
			Cursor c = Statics.DATABASEHELPER.getWritableDatabase().rawQuery("SELECT name, sql FROM sqlite_master WHERE type = 'table' ORDER BY name", null);

			while (c.moveToNext()) {
				existingTables.put(c.getString(0), c.getString(1));
			}
			c.close();
		} catch (Exception e) {
			if (e.getMessage() != null) {
				Log.e("checkTables()", e.getMessage());
			}
		}

		for (String m_sTableName : neededTables.keySet()) {
			Log.i("checkTables()", "CHECKING TABLE " + m_sTableName);
			

			try {
				if (existingTables.containsKey(m_sTableName)) {
					Log.i("checkTables()", "TABLE EXISTING");
					alterTable(m_sTableName, existingTables.get(m_sTableName), neededTables.get(m_sTableName));
				} else {
					Log.i("checkTables()", "TABLE NOT EXISTING");
					createTable(m_sTableName, neededTables.get(m_sTableName));
				}
			} catch (Exception e) {
				if (e.getMessage() != null) {
					Log.e("checkTables()", e.getMessage());
				}
			}
		}

		Log.i("checkTables()", "END");
	}

	public static void alterTable(String m_sTableName, String sqlStatement, Vector<Column> columns) {
		Log.i("alterTable()", "BEGIN");
		
		HashMap<String, Column> tmp = new HashMap<String, Column>();

		sqlStatement = sqlStatement.substring(sqlStatement.indexOf('(') + 1, sqlStatement.lastIndexOf(')'));
		String[] m_sColumns = sqlStatement.split(",");
		int index;
		boolean updateTable = false;		
		try {
			for (String m_sColumn : m_sColumns) {
				Column col = new Column();
				m_sColumn = m_sColumn.trim();
				String name = m_sColumn.substring(0, m_sColumn.indexOf(" "));
				col.m_sName = name;
				index = m_sColumn.indexOf(" ") + 1;
				m_sColumn = m_sColumn.substring(index).trim();
				String dataTyp = "";
				if (m_sColumn.contains(" ")) {
					dataTyp = m_sColumn.substring(0, m_sColumn.indexOf(" "));
				} else {
					dataTyp = m_sColumn;
				}
				if (dataTyp.toLowerCase().contains("varchar")) {
					col.m_cDatatype = ColumnDataType.sql_varchar;
					String sLength = dataTyp.substring(dataTyp.indexOf("(") + 1, dataTyp.indexOf(")")).trim();
					col.m_nLength = Integer.valueOf(sLength);
				} else if (dataTyp.toLowerCase().contains("int")) {
					col.m_cDatatype = ColumnDataType.sql_varchar;
				} else if (dataTyp.toLowerCase().contains("integer")) {

				}
				if (m_sColumn.contains(" ")) {
					m_sColumn = m_sColumn.substring(m_sColumn.indexOf(" ") + 1).trim();
					boolean pkey = false;
					if (m_sColumn.toLowerCase().contains("primary key"))
						pkey = true;
					col.m_bPrimaryKey = pkey;
				}
				tmp.put(name, col);
			}
			
			if (tmp.size() != columns.size()) {
				updateTable = true;
			}
		} catch (Exception ex) {
			updateTable = true;
		}
		
		if (updateTable) {
			Log.i("alterTable()", "TABLE NEEDS UPDATE");

			String sTransaction = "BEGIN TRANSACTION;\n";
			// Create tmp Table
			String sTransactionTmpTable = "CREATE TABLE TEMPORARY (";
			for (int i = 0; i < columns.size(); i++) {
				sTransactionTmpTable += columns.elementAt(i).getSqlDefinitionString();
				if (i < columns.size() - 1)
					sTransactionTmpTable += ", ";
			}
			sTransactionTmpTable += ");\n";
			// Copy Table
			String sTransactionInsertTmpTable = "INSERT INTO TEMPORARY (";
			boolean flag = false;
			String stmp = "";
			for (int i = 0; i < columns.size(); i++) {
				if (tmp.containsKey(columns.elementAt(i).m_sName)) {
					if (i > 0) {
						stmp += ", ";
					}
					stmp += columns.elementAt(i).m_sName;
				}
			}

			stmp = stmp.substring(0, stmp.lastIndexOf(","));
			sTransactionInsertTmpTable += stmp + ") SELECT " + stmp;
			sTransactionInsertTmpTable += " FROM " + m_sTableName + ";\n";
			String sTransactionDropTable = "DROP TABLE " + m_sTableName + ";\n";
			String sTransactionCreateTable = "CREATE TABLE " + m_sTableName + " (";
			for (int i = 0; i < columns.size(); i++) {
				sTransactionCreateTable += columns.elementAt(i).getSqlDefinitionString();
				if (i < columns.size() - 1)
					sTransactionCreateTable += ", ";
			}
			sTransactionCreateTable += ");\n";
			String sTransactionCopyTable = "INSERT INTO " + m_sTableName + " SELECT ";
			flag = false;
			for (int i = 0; i < columns.size(); i++) {
				if (i > 0) {
					sTransactionCopyTable += ", ";
				}
				sTransactionCopyTable += columns.elementAt(i).m_sName;
			}
			sTransactionCopyTable += " FROM TEMPORARY;\n";
			String sTransactionDropTmp = "DROP TABLE TEMPORARY;\n";
			sTransaction += "COMMIT;";

			try {
				Statics.DATABASEHELPER.getWritableDatabase().execSQL("DROP TABLE IF EXISTS TEMPORARY");
				Statics.DATABASEHELPER.getWritableDatabase().execSQL(sTransactionTmpTable);
				try {
					Statics.DATABASEHELPER.getWritableDatabase().execSQL(sTransactionInsertTmpTable);
				} catch (Exception ex2) {
					Statics.DATABASEHELPER.getWritableDatabase().execSQL("DROP TABLE IF EXISTS TEMPORARY");
					Statics.DATABASEHELPER.getWritableDatabase().execSQL(sTransactionTmpTable);
					copyByLine("TEMPORARY", m_sTableName, columns);
				}
				Statics.DATABASEHELPER.getWritableDatabase().execSQL(sTransactionDropTable);
				Statics.DATABASEHELPER.getWritableDatabase().execSQL(sTransactionCreateTable);
				Statics.DATABASEHELPER.getWritableDatabase().execSQL(sTransactionCopyTable);
				Statics.DATABASEHELPER.getWritableDatabase().execSQL("DROP TABLE IF EXISTS TEMPORARY");
				
				Log.i("alterTable()", "TABLE UPDATED");
			} catch (Exception e) {
				if (e.getMessage() != null) {
					Log.i("alterTable()", e.getMessage());
				}
			}
			Log.i("alterTable()", "END");
		}
	}

	private static void copyByLine(String sTmpName, String sOrgName, Vector<Column> columns) {
		Log.i("copyByLine()", "BEGIN");

		Cursor c = Statics.DATABASEHELPER.getWritableDatabase().rawQuery("SELECT sql FROM sqlite_master WHERE type='table' AND name = '" + sOrgName + "'", null);
		String tmp = "";
		while (c.moveToNext()) {
			tmp = c.getString(0);
		}

		tmp = tmp.substring(tmp.indexOf("("));

		String selectStatement = "SELECT ";
		for (Column col : columns) {
			if (tmp.contains(col.m_sName)) {
				selectStatement += col.m_sName + ", ";
			}
		}
		selectStatement = selectStatement.substring(0, selectStatement.lastIndexOf(","));
		selectStatement += " FROM " + sOrgName;

		c = Statics.DATABASEHELPER.getWritableDatabase().rawQuery(selectStatement, null);
		while (c.moveToNext()) {
			ContentValues values = new ContentValues();
			
			String insertStatement = "INSERT INTO " + sTmpName + " VALUES (";
			for (int i = 0; i < columns.size(); i++) {
				try {
					if (i > 0) {
						insertStatement += ", ";
					}

					System.out.println(columns.elementAt(i).m_sName);
					if (columns.elementAt(i).m_sName.equals("m_sDateCreate")) {
						boolean b = true;
					}
					
					if (columns.elementAt(i).m_cDatatype == ColumnDataType.sql_varchar) {
						if (c.getColumnIndex(columns.elementAt(i).m_sName) < 0) {
							values.put(columns.elementAt(i).m_sName, "");
						} else if (c.isNull(c.getColumnIndex(columns.elementAt(i).m_sName))) {
							values.put(columns.elementAt(i).m_sName, "");
						} else {
							values.put(columns.elementAt(i).m_sName, c.getString(c.getColumnIndex(columns.elementAt(i).m_sName)));
						}
					} else if (columns.elementAt(i).m_cDatatype == ColumnDataType.sql_integer) {
						if (c.getColumnIndex(columns.elementAt(i).m_sName) < 0)
							values.put(columns.elementAt(i).m_sName, 0);
						else
							values.put(columns.elementAt(i).m_sName, c.getInt(c.getColumnIndex(columns.elementAt(i).m_sName)));
					} else {
						values.put(columns.elementAt(i).m_sName, "");
					}
				} catch (Exception e) {
					if (e.getMessage() != null) {
						Log.e("copyByLine()", e.getMessage());
					}
				}

			}
			insertStatement += ");";

			try {
				Statics.DATABASEHELPER.getWritableDatabase().insert(sTmpName, null, values);
			} catch (Exception e) {
				if (e.getMessage() != null) {
					Log.e("copyByLine()", e.getMessage());
				}
			}

		}
		c.close();
		
		Log.i("copyByLine()", "END");
	}
	
	public static HashMap<String, Vector<Column>> getAllTables() {
		HashMap<String, Vector<Column>> neededTables = new HashMap<String, Vector<Column>>();

		neededTables.put(Password.TABLENAME, Password.getColumnConfig());
		neededTables.put(Settings.TABLENAME, Settings.getColumnConfig());
		neededTables.put(NFCTag.TABLENAME, NFCTag.getColumnConfig());
		
		return neededTables;
	}
	
	public static void deleteDatabase() {
		Log.i("deleteDatabase()", "BEGIN");
		
		File dataBase = new File(Statics.PATH_DATABASE);
		if (dataBase.exists()) {
			dataBase.delete();
		}
		
		Log.i("deleteDatabase()", "END");
	}

}
